﻿<%@ WebHandler Language="C#" Class="ZSDealFaultBillDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 排故返件看板
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.6.9
 */

public class ZSDealFaultBillDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                DataTable dtResult = new DataTable();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getEnginefamilyList":
                        r = getEnginefamilyList(jObject);//查询机型列表
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMonthTotalBarData":
                        r = getMonthTotalBarData(jObject);//返件统计--柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTotalBarDetail":
                        r = getTotalBarDetail(jObject);//返件统计--柱状图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportTotalBarDetail":
                        dtResult = dal.GetDataTable(getTotalBarDetailSql(jObject));//返件统计(柱状图)明细导出
                        exportExcelCommon(dtResult, "本月返件数据【" + jObject["paramName"].ToString() + "】明细");
                        break;
                    case "getFactoryData":
                        r = getFactoryData(jObject);//各分厂完成情况--柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryDetail":
                        r = getFactoryDetail(jObject);//各分厂完成情况--柱状图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportFactoryDetail":
                        dtResult = dal.GetDataTable(getFactoryDetailSql(jObject));//各分厂完成情况--柱状图明细导出
                        exportExcelCommon(dtResult, "【" + jObject["factoryname"].ToString() + "】本月各单位完成情况【" + jObject["paramName"].ToString() + "】明细");
                        break;
                    case "exportData":
                        dtResult = dal.GetDataTable(getDealFaultBillSql(jObject));//返件明细--导出
                        exportExcelCommon(dtResult, "排故返件看板导出数据");
                        break;
                    case "getFactoryFinishRate":
                        r = getFactoryFinishRate(jObject);//各返件单位完成率
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryFinishRateDetail":
                        r = getFactoryFinishRateDetail(jObject);//各返件单位完成率--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportFactoryFinishRateDetail":
                        dtResult = dal.GetDataTable(getFactoryFinishRateDetailSql(jObject));//各返件单位完成率--明细导出
                        exportExcelCommon(dtResult, jObject["factoryname"].ToString() + "【" + jObject["paramName"].ToString() + "】明细");
                        break;
                    case "getProductFamilyChartData":
                        r = getProductFamilyChartData(jObject);//各机型完成情况
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getProductFamilyChartDetail":
                        r = getProductFamilyChartDetail(jObject);//各机型完成情况--明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportEnginefamilyDetail":
                        dtResult = dal.GetDataTable(getProductFamilyChartDetailSql(jObject));//各机型完成情况--导出
                        exportExcelCommon(dtResult, jObject["enginefamily"].ToString() + "【" + jObject["paramName"].ToString() + "】明细");
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询机型列表
    Result getEnginefamilyList(JObject jObject)
    {
        string sql = "SELECT DISTINCT db.enginefamily FROM dealfaultbill db WHERE db.enginefamily IS NOT NULL";
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //返件查询sql
    string getDealFaultBillSql(JObject jObject)
    {
        string sql = @"
SELECT  NVL(db.enginefamily,'机型为空') enginefamily,db.dealfaultbillname,db.engineno,db.productname,p.description productdescription,
        db.productbatchno,f2.factoryname fromfactoryname,f.factoryname,
        CASE WHEN db.transferstatus = 0 THEN '待返出单位技术审核'
             WHEN db.transferstatus = 1 THEN '待返出单位检验确认'
             WHEN db.transferstatus = 2 THEN '待返出单位返出'
             WHEN db.transferstatus = 3 THEN '待接收单位接收'
             WHEN db.transferstatus = 4 THEN '待接收单位完成'
             WHEN db.transferstatus = 8 AND dbt.faultcheckreportno IS NULL THEN '待检验故检报告'
             WHEN db.transferstatus = 8 AND dbt.faultcheckreportno IS NOT NULL AND dbt.dealfileno IS NULL THEN '待工程部修理文件'
             WHEN db.transferstatus = 5 AND dbt.dealfileno IS NOT NULL THEN '待接收单位修理'
             WHEN db.transferstatus = 5 AND dbt.faultcheckdate IS NULL THEN '待接收单位返回'
             WHEN db.transferstatus = 6 THEN '待返出单位领取'
             WHEN db.transferstatus = 7 THEN '现场已领取'
        END status,
        dbt.fromtime,dbt.requirefinishtime,dbt.RecvFactoryTime,NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) recvfactoryreturntime,
        dbt.recvfactoryreturntime returndate,
        CASE WHEN dbt.requirefinishtime IS NULL THEN 0
             WHEN NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NOT NULL 
               THEN TRUNC(NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime)) - TRUNC(dbt.requirefinishtime)
             ELSE TRUNC(SYSDATE) - TRUNC(dbt.requirefinishtime)
        END delayDays,
        CASE WHEN dbt.requirefinishtime IS NOT NULL 
             AND NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NULL 
             AND TRUNC(SYSDATE) = TRUNC(dbt.requirefinishtime) THEN 1 
             ELSE 0 
        END willDelay,
        db.faultdesc,
        DECODE(dbt.faultcheckempid,NULL,'否','是') ischeck,dbt.entitystoragelocation,dbt.faultcheckreportno,dbt.dealfileno,
        dbt.faultcheckdate,dbt.faultcheckreportnodate,
        CASE WHEN dbt.faultcheckdate IS NOT NULL THEN dbt.finishtime
             ELSE NULL 
        END jsdate,
        CASE WHEN dbt.faultcheckreportnodate IS NOT NULL AND dbt.faultcheckdate IS NOT NULL 
             THEN TRUNC(dbt.faultcheckreportnodate) - TRUNC(dbt.faultcheckdate)
             ELSE NULL 
        END gjbgDays,
        CASE WHEN dbt.faultcheckdate IS NOT NULL AND dbt.finishtime IS NOT NULL AND dbt.faultcheckreportnodate IS NOT NULL
             THEN TRUNC(dbt.finishtime) - TRUNC(dbt.faultcheckreportnodate)
             ELSE NULL
        END xlwjDays,
        CASE WHEN dbt.faultcheckdate IS NOT NULL AND dbt.finishtime IS NOT NULL AND dbt.recvfactoryreturntime IS NOT NULL
             THEN TRUNC(dbt.recvfactoryreturntime) - TRUNC(dbt.finishtime)
             ELSE NULL
        END xlDays
FROM DealFaultBill db
LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
LEFT JOIN mes_factory_mapping f ON f.factoryid = db.returnfactoryid
LEFT JOIN employee e ON e.employeeid = db.createemployeeid
LEFT JOIN Sessionvalues sv ON sv.employeeid = e.employeeid
LEFT JOIN productbase pb ON pb.productname = db.productname
LEFT JOIN product p ON p.productid = pb.revofrcdid
LEFT JOIN mes_factory_mapping f2 ON f2.factoryid = sv.factoryid
WHERE NVL(db.isuse,0) = 0 AND NVL(dbt.isuse,0) = 0";

        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND sv.factoryid = '{0}' ", jObject["factoryid"].ToString());
        }

        sql = string.Format(sql, jObject["factoryid"].ToString());

        if (jObject["enginefamily"] != null && jObject["enginefamily"].ToString() != "")
        {
            if (jObject["enginefamily"].ToString() == "机型为空")
            {
                sql += " AND db.enginefamily IS NULL ";
            }
            else
            {
                sql += string.Format(" AND db.enginefamily = '{0}' ", jObject["enginefamily"].ToString());
            }
        }

        //要求完成时间
        if (jObject["requirefinishtime"] != null && jObject["requirefinishtime"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["requirefinishtime"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND dbt.requirefinishtime >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND dbt.requirefinishtime <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", arr[1].ToString());
            }
        }

        //排故单号过滤
        if (jObject["dealfaultbillname"] != null && jObject["dealfaultbillname"].ToString() != "")
        {
            sql += string.Format(" AND db.dealfaultbillname = '{0}'", jObject["dealfaultbillname"].ToString());
        }
        return sql;
    }

    //本月返件数据统计
    Result getMonthTotalBarData(JObject jObject)
    {
        string sql = getDealFaultBillSql(jObject);
        DataTable dt = dal.GetDataTable(sql);

        DataTable dtResult = new DataTable();
        dtResult.Columns.Add("total");//总数
        dtResult.Columns.Add("fanChuQty");//已转出
        dtResult.Columns.Add("willDelayQty");//预超期
        dtResult.Columns.Add("intimeFinishQty");//按时完成
        dtResult.Columns.Add("delayNoFinish");//超期未完
        dtResult.Columns.Add("delayFinish");//超期已完
        dtResult.Columns.Add("checkqty");//待故检报告
        dtResult.Columns.Add("dealqty");//待修理文件
        dtResult.Columns.Add("gjToReturn");//待接收单位修理

        DataRow dr = dtResult.NewRow();
        dr["total"] = dt.Rows.Count;
        dr["fanChuQty"] = dt.Select("fromtime is not null").Length;
        dr["willDelayQty"] = dt.Select(" willDelay = '1' ").Length;
        dr["intimeFinishQty"] = dt.Select("recvfactoryreturntime is not null AND delayDays <= 0").Length;
        dr["delayNoFinish"] = dt.Select(" recvfactoryreturntime is null and delayDays > 0").Length;
        dr["delayFinish"] = dt.Select(" recvfactoryreturntime is not null and delayDays > 0").Length;
        dr["checkqty"] = dt.Select(" ischeck = '是' AND faultcheckreportno IS NULL").Length;
        dr["dealqty"] = dt.Select(" ischeck = '是' AND faultcheckreportno IS NOT NULL AND dealfileno IS NULL").Length;
        dr["gjToReturn"] = dt.Select(" status = '待接收单位修理'").Length;

        dtResult.Rows.Add(dr);

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getTotalBarDetailSql(JObject jObject)
    {
        string sql = getDealFaultBillSql(jObject);
        if (jObject["paramName"].ToString() == "计划数")
        {
            sql += " AND fromtime IS NOT NULL ";
        }
        if (jObject["paramName"].ToString() == "完成数")
        {
            sql += @" AND NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NOT NULL";
        }
        if (jObject["paramName"].ToString() == "未完数")
        {
            sql += @" AND NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NULL";
        }
//        if (jObject["paramName"].ToString() == "超期未完")
//        {
//            sql += @" 
//AND dbt.requirefinishtime IS NOT NULL 
//AND NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NULL 
//AND TRUNC(SYSDATE) > TRUNC(dbt.requirefinishtime)";
//        }
        return sql;
    }

    Result getTotalBarDetail(JObject jObject)
    {
        string sql = getTotalBarDetailSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    void exportExcelCommon(DataTable dtResult, string filename)
    {
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dtResult.Rows)
        {
            if (dr["delaydays"] != null && Convert.ToInt32(dr["delaydays"]) < 0)
            {
                dr["delaydays"] = "0";
            }
        }
        dtResult.Columns.Remove("willdelay");
        dtResult.Columns["enginefamily"].Caption = "机型";
        dtResult.Columns["dealfaultbillname"].Caption = "返件编号";
        dtResult.Columns["engineno"].Caption = "发动机号";
        dtResult.Columns["productname"].Caption = "件号";
        dtResult.Columns["productbatchno"].Caption = "批次";
        dtResult.Columns["fromfactoryname"].Caption = "返出单位";
        dtResult.Columns["factoryname"].Caption = "接收单位";
        dtResult.Columns["status"].Caption = "排故进展";
        dtResult.Columns["fromtime"].Caption = "返出时间";
        dtResult.Columns["requirefinishtime"].Caption = "要求完成时间";
        dtResult.Columns["delaydays"].Caption = "超期天数";
        dtResult.Columns["recvfactorytime"].Caption = "接收时间";
        dtResult.Columns["recvfactoryreturntime"].Caption = "完成时间";
        dtResult.Columns["returndate"].Caption = "返入时间";
        dtResult.Columns["faultdesc"].Caption = "故障内容";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["ischeck"].Caption = "是否故检";
        dtResult.Columns["entitystoragelocation"].Caption = "实物存放地";
        dtResult.Columns["faultcheckreportno"].Caption = "故检报告号";
        dtResult.Columns["dealfileno"].Caption = "修理文件号";
        dtResult.Columns["faultcheckdate"].Caption = "提交故检时间";
        dtResult.Columns["faultcheckreportnodate"].Caption = "故检报告号录入日期";
        dtResult.Columns["jsdate"].Caption = "修理文件录入时间";
        dtResult.Columns["gjbgDays"].Caption = "故检报告天数";
        dtResult.Columns["xlwjDays"].Caption = "修理文件天数";
        dtResult.Columns["xlDays"].Caption = "修理天数";
        ExportHelper.ExportExcel(dtResult, filename);
    }

    Result getFactoryData(JObject jObject)
    {
        string sql = getDealFaultBillSql(jObject);
        DataTable dt = dal.GetDataTable(sql);

        foreach (DataRow dr in dt.Rows)
        {
            if (dr["factoryname"].ToString() == "无损检测中心")
            {
                dr["factoryname"] = "检验检测中心";
            }
        }

        DataTable dtResult = dt.DefaultView.ToTable(true, "factoryname");
        dtResult.Columns.Add("total");//总数
        dtResult.Columns.Add("fanChuQty");//已转出
        dtResult.Columns.Add("willDelayQty");//预超期
        dtResult.Columns.Add("intimeFinishQty");//按时完成
        dtResult.Columns.Add("delayNoFinish");//超期未完
        dtResult.Columns.Add("delayFinish");//超期已完
        dtResult.Columns.Add("checkqty");//待故检报告
        dtResult.Columns.Add("dealqty");//待修理文件

        foreach (DataRow dr in dtResult.Rows)
        {
            dr["total"] = dt.Select(string.Format("factoryname = '{0}'", dr["factoryname"].ToString())).Length;
            dr["fanChuQty"] = dt.Select(string.Format("factoryname = '{0}' AND fromtime is not null", dr["factoryname"].ToString())).Length;
            dr["willDelayQty"] = dt.Select(string.Format("factoryname = '{0}' AND willDelay = '1'", dr["factoryname"].ToString())).Length;
            dr["intimeFinishQty"] = dt.Select(string.Format("factoryname = '{0}' AND recvfactoryreturntime is not null AND delayDays <= 0",
                                              dr["factoryname"].ToString())).Length;
            dr["delayNoFinish"] = dt.Select(string.Format("factoryname = '{0}' AND recvfactoryreturntime is null and delayDays > 0 ", dr["factoryname"].ToString())).Length;
            dr["delayFinish"] = dt.Select(string.Format("factoryname = '{0}' AND recvfactoryreturntime is not null and delayDays > 0", dr["factoryname"].ToString())).Length;
            dr["checkqty"] = dt.Select(string.Format("factoryname = '{0}' AND ischeck = '是' AND faultcheckreportno IS NULL", dr["factoryname"].ToString())).Length;
            dr["dealqty"] = dt.Select(string.Format("factoryname = '{0}' AND ischeck = '是' AND faultcheckreportno IS NOT NULL AND dealfileno IS NULL",
                                                    dr["factoryname"].ToString())).Length;
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getFactoryDetailSql(JObject jObject)
    {
        string sql = getDealFaultBillSql(jObject);
        if (jObject["paramName"].ToString() == "计划数")
        {
            sql += " AND fromtime IS NOT NULL ";
        }
        if (jObject["paramName"].ToString() == "完成数")
        {
            sql += @" AND NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NOT NULL";
        }
        if (jObject["paramName"].ToString() == "未完数")
        {
            sql += @" AND NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NULL";
        }
//        if (jObject["paramName"].ToString() == "超期未完")
//        {
//            sql += @" 
//AND dbt.requirefinishtime IS NOT NULL 
//AND NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NULL 
//AND TRUNC(SYSDATE) > TRUNC(dbt.requirefinishtime)";
//        }
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            if (jObject["factoryname"].ToString() == "检验检测中心")
            {
                sql += " AND f.factoryname IN ('检验检测中心','无损检测中心')";
            }
            else
            {
                sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
            }
        }
        return sql;
    }

    Result getFactoryDetail(JObject jObject)
    {
        string sql = getFactoryDetailSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getFactoryFinishRate(JObject jObject)
    {
        string sql = getDealFaultBillSql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataRow dr in dt.Rows)
        {
            if (dr["factoryname"].ToString() == "无损检测中心")
            {
                dr["factoryname"] = "检验检测中心";
            }
        }

        DataTable dtResult = dt.DefaultView.ToTable(true, "factoryname");
        dtResult.Columns.Add("total");//总数
        dtResult.Columns.Add("finishedqty");//完成数
        dtResult.Columns.Add("intimefinishedqty");//按时完成数
        dtResult.Columns.Add("finishRate");//完成率
        dtResult.Columns.Add("intimeFinishedRate");//按时完成率

        foreach (DataRow dr in dtResult.Rows)
        {
            dr["total"] = dt.Select(string.Format("factoryname = '{0}'", dr["factoryname"].ToString())).Length;
            dr["finishedqty"] = dt.Select(string.Format("factoryname = '{0}' AND recvfactoryreturntime is not null", dr["factoryname"].ToString())).Length;
            dr["intimefinishedqty"] = dt.Select(string.Format("factoryname = '{0}' AND recvfactoryreturntime IS NOT NULL and delayDays <= 0 ", dr["factoryname"].ToString())).Length;

            decimal finishRate = ((decimal)Convert.ToInt32(dr["finishedqty"].ToString()) / Convert.ToInt32(dr["total"].ToString())) * 100;
            dr["finishRate"] = finishRate.ToString("0.00");

            decimal intimeFinishedRate = ((decimal)Convert.ToInt32(dr["intimefinishedqty"].ToString()) / Convert.ToInt32(dr["total"].ToString())) * 100;
            dr["intimeFinishedRate"] = intimeFinishedRate.ToString("0.00");
        }

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getFactoryFinishRateDetailSql(JObject jObject)
    {
        string sql = getDealFaultBillSql(jObject);

        if (jObject["paramName"].ToString() == "完成率")
        {
            sql += " AND dbt.recvFactoryReturnTime IS NOT NULL ";
        }

        if (jObject["paramName"].ToString() == "按时完成率")
        {
            sql += @" 
AND NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NOT NULL 
AND TRUNC(NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime)) <= TRUNC(dbt.requirefinishtime) ";
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            if (jObject["factoryname"].ToString() == "检验检测中心")
            {
                sql += " AND f.factoryname IN ('检验检测中心','无损检测中心')";
            }
            else
            {
                sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
            }
        }
        return sql;
    }

    Result getFactoryFinishRateDetail(JObject jObject)
    {
        string sql = getFactoryFinishRateDetailSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    // 各机型完成情况
    Result getProductFamilyChartData(JObject jObject)
    {
        string sql = @"
SELECT t.enginefamily,
       COUNT(1) total,
       SUM(CASE WHEN NVL(t.faultcheckdate,t.recvfactoryreturntime) IS NOT NULL THEN 1 ELSE 0 END) finishedqty,
       SUM(CASE WHEN NVL(t.faultcheckdate,t.recvfactoryreturntime) IS NULL THEN 1 ELSE 0 END) unfinishedqty
FROM (
    {0}
) t
GROUP BY t.enginefamily
ORDER BY t.enginefamily";

        sql = string.Format(sql, getDealFaultBillSql(jObject));
        DataTable dtResult = dal.GetDataTable(sql);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各机型完成情况--明细查询sql
    string getProductFamilyChartDetailSql(JObject jObject)
    {
        string sql = getDealFaultBillSql(jObject);
        if (jObject["paramName"].ToString() == "计划数")
        {
            sql += " AND fromtime IS NOT NULL ";
        }
        if (jObject["paramName"].ToString() == "完成数")
        {
            sql += @" AND NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NOT NULL";
        }
        if (jObject["paramName"].ToString() == "未完数")
        {
            sql += @" AND NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NULL";
        }
//        if (jObject["paramName"].ToString() == "按时完成")
//        {
//            sql += @" 
//AND dbt.requirefinishtime IS NOT NULL
//AND NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime) IS NOT NULL
//AND TRUNC(NVL(dbt.faultcheckdate,dbt.recvfactoryreturntime)) <= TRUNC(dbt.requirefinishtime)";
//        }
        return sql;
    }

    //各机型完成情况--明细
    Result getProductFamilyChartDetail(JObject jObject)
    {
        string sql = getProductFamilyChartDetailSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}